﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CMP.ERP.Model;
using System.Data.SqlClient;
using System.Data;
using CMP.ERP.Helper;
namespace CMP.ERP.DAL
{
    public class UserDAL
    {
        string conn = "Data Source=169.254.4.161;Initial Catalog=FourGroupERP;User ID=sa;Pwd=111111";

        /// <summary>
        /// 实例化SQLHelper对象
        /// </summary>
        SQLHelper p = new SQLHelper();

        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool GetShow(string name, string pwd)
        {
            SqlConnection con = new SqlConnection(conn);
            con.Open();
            string sql = "select * from Users where UName='" + name + "' and UPwd='" + pwd + "' and Ustate='正常'";
            SqlCommand com = new SqlCommand(sql, con);
            int v = Convert.ToInt32(com.ExecuteScalar());
            con.Close();
            return v > 0;

        }
        /// <summary>
        /// 判断用户是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int GetShow1(string name)
        {
            SqlConnection con = new SqlConnection(conn);
            con.Open();
            string sql = "select Count(*) from Users where UName='" + name + "'";
            SqlCommand com = new SqlCommand(sql, con);
            int v = Convert.ToInt32(com.ExecuteScalar());
            return v;

        }
        /// <summary>
        /// 注册
        /// </summary>
        /// <param name="uname"></param>
        /// <param name="upwd"></param>
        /// <returns></returns>
        public bool Getadd(Users u)
        {
            SqlConnection con = new SqlConnection(conn);
            con.Open();
            string sql = "insert into Users values('" + u.UName + "','" + u.UPwd + "','" + u.CompanyName + "','"+u.Ustate+"')";
            SqlCommand com = new SqlCommand(sql, con);
            int h = com.ExecuteNonQuery();
            con.Close();
            return h > 0;
        }
        /// <summary>
        /// 显示
        /// </summary>
        /// <returns></returns>
        public DataTable ShowUsers()
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_ShowUsers");
            SqlParameter[] par = new SqlParameter[]{
            };
            return p.ExecDataTable_Proc(buil.ToString(),par);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="UIds"></param>
        /// <returns></returns>
        public int DelUsers(int UIds)
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_DelUsers");
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@UIds",UIds)
            };
            return p.ExecNonquery_Proc(buil.ToString(), par);
        }
        /// <summary>
        /// 根据编号查询用户信息
        /// </summary>
        /// <param name="UIds"></param>
        /// <returns></returns>
        public DataTable GetUsers(int UIds)
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_GetUsers");
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@UIds",UIds)
            };
            return p.ExecDataTable_Proc(buil.ToString(), par);
        }
        /// <summary>
        /// 根据名称查询用户信息
        /// </summary>
        /// <param name="UName"></param>
        /// <returns></returns>
        public DataTable SelUsers(string UName)
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_SelUsers");
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@UName",UName)
            };
            return p.ExecDataTable_Proc(buil.ToString(), par);
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public int UpdUsers(Users u)
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_UpdUsers");
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@UName",u.UName),
                new SqlParameter("@UPwd",u.UPwd),
                new SqlParameter("@CompanyName",u.CompanyName),
                new SqlParameter("@UIds",u.UIds)
            };
            return p.ExecNonquery_Proc(buil.ToString(), par);
        }
        public int StateUsers(Users u)
        {
            StringBuilder buil = new StringBuilder();
            buil.Append("Proc_StateUsers");
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@Ustate",u.Ustate),
                new SqlParameter("@UIds",u.UIds)
            };
            return p.ExecNonquery_Proc(buil.ToString(), par);
        }
    }
}

